#!/usr/bin/env python

from pysqlite2 import dbapi2 as sqlite

def pp(cursor, data=None, rowlens=0):
    d = cursor.description
    if not d:
        return "#### NO RESULTS ###"
    names = []
    lengths = []
    rules = []
    if not data:
        t = cursor.fetchall()
    for dd in d:    # iterate over description
        l = dd[1]
        if not l:
            l = 12             # or default arg ...
        l = max(l, len(dd[0])) # handle long names
        names.append(dd[0])
        lengths.append(l)
    for col in range(len(lengths)):
        if rowlens:
            rls = [len(str(row[col])) for row in data if row[col]]
            lengths[col] = max([lengths[col]]+rls)
        rules.append("-"*lengths[col])
    format = " ".join(["%%-%ss" % l for l in lengths])
    result = [format % tuple(names)]
    result.append(format % tuple(rules))
    for row in data:
        result.append(format % row)
    return "\n".join(result)

class DBController:
	def __init__(self):
		self.connection=sqlite.connect('marich.sqlite')
		self.cursor=self.connection.cursor()
		self.current_season = 1
		
	def inventory_retrieval(self, cat):
		cursor = self.cursor
		connection = self.connection
		select = "SELECT * FROM Inventory where "
		ctr = 0
		if len(cat) != 0:
			for i in cat:
				ctr = ctr + 1
				if ctr == 1:
					select = select + 'Inventory.Crop = "' + cat[0] + '" '
				elif ctr == 2:
					if cat[0] != 'Yellow Corn':
						select = select + 'and Inventory.Variety = "' + cat[1] + '" '
					else:
						select = select + 'and Inventory.Grade = "' + cat[1] + '"'
				else:
					if cat[0] != 'Yellow Corn':
						select = select + 'and Inventory.Grade = "' + cat[2] + '"'
			select+' ORDER BY Date'
			cursor.execute(select)
			b = cursor.fetchall()
			balance_count = 0
			out = []
			for i in b:
				if i[5] == 0:
					balance_count = balance_count + i[6]
					out.append([i[4], i[1], i[6], "", balance_count, i[7]])
				else:
					balance_count = balance_count - i[6]
					out.append([i[4], i[1], "", i[6], balance_count, i[7]])
			print out
			return out
			
	def inventorycursor_retrieval(self):
		self.cursor.execute("SELECT Date, Crop, Variety, Grade, Type, Weight, Name FROM Inventory")
		row = self.cursor.fetchall()
		out = pp(self.cursor, row, rowlens = 10)
		print out
		return out
			
	def inventory_gradescount(self, crop, grades):
		out = []
		labels = []
		for x in grades:
			self.cursor.execute('SELECT Type, Weight FROM Inventory WHERE Crop=? AND Grade=?', (crop, x))
			value = self.cursor.fetchall()
			total = 0
			for a in value:
				if a[0] == 0:
					total = total + a[1]
				else:
					total = total - a[1]
			if total>0:
				out.append(int(total))
				labels.append(x)

		return out, labels
		
	def inventory_varietycount(self, varieties):
		out = []
		labels = []
		for x in varieties:
			self.cursor.execute('SELECT Type, Weight FROM Inventory WHERE Crop="Palay" AND Variety=?', (x,))
			value = self.cursor.fetchall()
			total = 0
			for a in value:
				if a[0] == 0:
					total = total + a[1]
				else:
					total = total - a[1]
			if total>0:
				out.append(int(total))
				labels.append(x)
		
		return out, labels
			
	def inventory_add(self, id, date, crop, variety, grade, type, weight, name):
		self.cursor.execute('INSERT INTO Inventory (EntryNumber, Crop, Variety, Grade, Date, Type, Weight, Name) VALUES (?,?,?,?,?,?,?,?)', (id, crop, variety, grade, date, type, weight, name))
		self.connection.commit()
	
	def inventory_update(self, id, date, crop, variety, grade, type, weight, name):
		self.cursor.execute('UPDATE Inventory SET Crop=?, Variety=?, Grade=?, Date=?, Type=?, Weight=?, Name=? WHERE EntryNumber = ?', (crop, variety, grade, date, type, weight, name, id))
		self.connection.commit()
                    
	def client_retrieval(self):
		cursor = self.cursor
		connection = self.connection
		select = "SELECT FirstName || ' ' || MiddleName || ' ' || LastName as Name, Address, Balance, ClientId FROM ClientInformation"
		cursor.execute(select)
		
		a = cursor.fetchall()
		out = []
		if len(a) != 0:
			for i in a:
				out.append([i[0],i[1],i[2], i[3]])
		print out
		return out
		
	def clientcursor_retrieval(self):
		self.cursor.execute("SELECT FirstName || ' ' || MiddleName || ' ' || LastName as Name, Address, Balance FROM ClientInformation")
		row = self.cursor.fetchall()
		out = pp(self.cursor, row, rowlens = 10)
		print out
		return out

	def add_client_to_db(self, client_id, firstname, middlename, lastname, address):
		cursor = self.cursor
		connection = self.connection
		cursor.execute("INSERT into ClientInformation (ClientId, LastName, FirstName, MiddleName, Address, Balance) values (?, ?, ?, ?, ?, ?)", (client_id, lastname, firstname, middlename, address, 0))
		connection.commit()

	def edit_client_on_db(self, client_id, firstname, middlename, lastname, address):
		self.cursor.execute('UPDATE ClientInformation SET LastName=?, FirstName=?, MiddleName=?, Address=? WHERE ClientId = ?', (lastname, firstname, middlename, address, client_id))
		self.connection.commit()
	
	def get_clientname(self, client_id):
		self.cursor.execute('SELECT FirstName, MiddleName, LastName, Address FROM ClientInformation WHERE ClientId = ?', (client_id,))
		out = self.cursor.fetchone()
		return out
		
	def update_clientbalance(self, id, balance):
		self.cursor.execute('UPDATE ClientInformation SET Balance=? WHERE ClientId = ?', (balance, id))
		self.connection.commit()
		
	def client_contactnumbers_retrieval(self, id):
		cursor = self.cursor
		connection = self.connection
		cursor.execute('SELECT ContactNumber FROM ClientContact WHERE ClientID = ?', (id,))
		values = cursor.fetchall()
		out = []
		for i in values:
			out.append(str(i[0]))
		
		return out
		
	def client_addcontactnumber(self, id, number):
		cursor = self.cursor
		connection = self.connection
		cursor.execute('INSERT INTO ClientContact (ClientId, ContactNumber) values (?, ?)', (id, number))
		connection.commit()
		
	def client_deletecontactnumber(self, id, number):
		cursor = self.cursor
		connection = self.connection
		cursor.execute('DELETE FROM ClientContact WHERE ClientID = ? and ContactNumber = ?', (id, number))
		connection.commit()
		
	def clientbalance_retrieval(self, id):
		self.cursor.execute('SELECT BalanceNo, SeasonId, Date, Interest, Balance, ComputedTotal FROM ClientBalance WHERE ClientId = ?', (id,))
		raw = self.cursor.fetchall()
		out=[]
		for x in raw:
			#modify to implement season ek-ek
			out.append([x[2], x[5], x[3], x[4], x[0]])
		print out
		return out
		
	def clientbalance_add(self, id, date, interest):
		self.cursor.execute('SELECT BalanceNo, Total FROM ClientBalance WHERE Date = ? AND ClientId=?',(date, id))
		balancenum = self.cursor.fetchone() 
		print balancenum
		total = 0
		if not balancenum:
			print 'Create entry for date'
			self.cursor.execute('INSERT INTO ClientBalance (ClientId, SeasonId, Date, Total, Interest, Balance, ComputedTotal) VALUES(?,?,?,?,?,?,?)', (id, self.current_season, date, 0, interest, 0, 0))
			self.connection.commit()
			out = self.cursor.lastrowid
		else:
			out = balancenum[0]
			total = balancenum[1]
		return out, total
	
	def clientbalance_update(self, id, balancenum, total, interest, computedtotal):
		self.cursor.execute('UPDATE ClientBalance SET Total = ?, Interest=?, ComputedTotal=? WHERE ClientId = ? AND BalanceNo = ?', (total, interest, computedtotal, id, balancenum))
		self.connection.commit()
	
	def clientbalancecol_update(self, id, balance):
		self.cursor.execute('UPDATE ClientBalance SET Balance = ? WHERE ClientId = ?', (balance, id))
		self.connection.commit()
			
	
	def clientbalancedetails_retrieval(self, id, balancenum):
		self.cursor.execute('SELECT Item, UnitCost, Quantity, Total, BalanceNo, BalanceId FROM ClientBalanceDetails WHERE ClientId = ? AND BalanceNo =?', (id, balancenum))
		raw = self.cursor.fetchall()
		print raw
		return raw
		
	def clientbalancedetails_gettotal(self, balanceid):
		self.cursor.execute('SELECT Total FROM ClientBalanceDetails WHERE BalanceId = ?', (balanceid,))
		data = self.cursor.fetchone()
		return data[0]
	
	def clientbalancedetails_add(self, id, balancenum, item, unitcost, quantity, total, balanceid):
		self.cursor.execute('INSERT INTO ClientBalanceDetails (ClientId, BalanceNo, Item, UnitCost, Quantity, Total, BalanceId) VALUES(?,?,?,?,?,?,?)', (id, balancenum, item, unitcost, quantity, total, balanceid))
		self.connection.commit()
	
	def clientbalancedetails_update(self, item, unitcost, quantity, total, balanceid):
		self.cursor.execute('UPDATE ClientBalanceDetails SET Item=?, UnitCost=?, Quantity=?, Total=? WHERE BalanceId=?', (item, unitcost, quantity, total, balanceid))
		self.connection.commit()
	
	def clientpayment_retrieval(self, id):
		self.cursor.execute('SELECT SeasonID, Date, Crop, Variety, Grade, UnitCost, Quantity, Bilad, Total, Weight, TransactionId FROM ClientPayment WHERE ClientId = ?', (id,))
		raw = self.cursor.fetchall()
		out = []
		for x in raw:
			#modify this for season ek-ek
			out.append([x[1],x[2],x[3],x[4],x[5],x[6],x[7],x[8],x[9],x[10]])
		print out
		return out
		
	def clientpayment_add(self, id, transactionid, date, crop, variety, grade, cost, quantity, bilad, total, weight):
		self.cursor.execute('INSERT INTO ClientPayment (ClientId, TransactionId, Date, Crop, Variety, Grade, UnitCost, Quantity, Bilad, Total, Weight, SeasonId) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)', (id, transactionid, date, crop, variety, grade, cost, quantity, bilad, total, weight, self.current_season))
		name = self.get_clientname(id)
		self.inventory_add(transactionid, date, crop, variety, grade, 0, weight, name[0]+' '+name[2])
		self.connection.commit()
		
	def clientpayment_update(self, date, crop, variety, grade, cost, quantity, bilad, total, weight, transactionid, clientid):
		self.cursor.execute('UPDATE ClientPayment SET Date=?, Crop=?, Variety=?, Grade=?, UnitCost=?, Quantity=?, Bilad=?, Total=?, Weight=? WHERE TransactionId=?', (date, crop, variety, grade, cost, quantity, bilad, total, weight, transactionid))
		name = self.get_clientname(clientid)
		self.inventory_update(transactionid, date, crop, variety, grade, 0, weight, name[0]+' '+name[2])
		self.connection.commit()

	def companycursor_retrieval(self):
		self.cursor.execute("SELECT Company, Address FROM CustomerCompany")
		row = self.cursor.fetchall()
		out = pp(self.cursor, row, rowlens = 10)
		print out
		return out

	def company_retrieval(self):
		cursor = self.cursor
		connection = self.connection
		cursor.execute('SELECT Company, CompanyId FROM CustomerCompany')
		b = cursor.fetchall()
		out = []
		for i in b:
			out.append([i[0],i[1]])
		print out
		return out
		
	def get_companyname(self, id):
		self.cursor.execute('SELECT Company FROM CustomerCompany WHERE CompanyId = ?', (id,))
		name = self.cursor.fetchone()
		return name[0]
		
	def get_companyinfo(self, id):
		self.cursor.execute('SELECT Company, Address FROM CustomerCompany WHERE CompanyId = ?', (id,))
		b = self.cursor.fetchone()
		out = []
		for x in b:
			out.append(x)
		return out

	def company_contact_retrieval(self, id):
		cursor = self.cursor
		connection = self.connection
		cursor.execute('SELECT ContactNumber FROM CustomerContact WHERE CompanyId = ?', (id,))
		b = cursor.fetchall()
		out = []
		for i in b:
				out.append(i[0])
		return out
	
	def add_companycontact_to_db(self, id, number):
		self.cursor.execute('INSERT INTO CustomerContact (CompanyId, ContactNumber) values (?,?)', (id, number))
		self.connection.commit()
		
	def del_companycontact(self, id, number):
		self.cursor.execute('DELETE FROM CustomerContact WHERE CompanyId = ? AND ContactNumber = ?', (id, number))
		self.connection.commit()
					
	def company_contact_person_retrieval(self, id):
		cursor = self.cursor
		connection = self.connection
		cursor.execute('SELECT Name FROM CustomerContactPerson where CompanyId = ?', (id,))
		b = cursor.fetchall()
		out = []
		for i in b:
			out.append(i[0])
		
		print out
		return out

	def add_contactperson_to_db(self, id, name):
		self.cursor.execute('INSERT INTO CustomerContactPerson (CompanyId, Name) values (?, ?)', (id, name))
		self.connection.commit()
		
	def del_contactperson(self, id, name):
		self.cursor.execute('DELETE FROM CustomerContactPerson WHERE CompanyId = ? AND Name = ?', (id, name))
		self.connection.commit()
	
	def add_company_to_db(self, id, name, address):
		cursor = self.cursor
		connection = self.connection
		cursor.execute('INSERT INTO CustomerCompany (CompanyId, Company, Address) values (?, ?, ?)', (id, name, address))
		connection.commit()
		
	def edit_company(self, id, name, address):
		self.cursor.execute('UPDATE CustomerCompany SET Company = ?, Address = ? WHERE CompanyId = ?', (name, address, id))
		self.connection.commit()
	
	def contract_retrieval(self, id):
		self.cursor.execute('SELECT Date, Weight, UnitPrice, Served, Remaining, ContractNo FROM CustomerContract WHERE CompanyId = ?',(id,))
		out = self.cursor.fetchall()
		print out
		return out
	
	def add_contract(self, id, date, weight, price):
		self.cursor.execute('INSERT INTO CustomerContract (CompanyId, Date, Weight, UnitPrice, Served, Remaining, SeasonId) VALUES (?,?,?,?,?,?,?)', (id, date, weight, price, 0, weight, self.current_season))
		contractno = self.cursor.lastrowid
		self.connection.commit()
		return contractno
		
	def update_contract(self, contractno, date, weight, price, served, remaining):
		self.cursor.execute('UPDATE CustomerContract SET Date=?, Weight=?, UnitPrice=?, Served=?, Remaining=? WHERE ContractNo = ?', (date, weight, price, served, remaining, contractno))
		self.connection.commit()
	
	def compute_weightserved(self, contractno):
		self.cursor.execute('SELECT SUM(ManilaWeight) FROM Delivery WHERE ContractNo = ?', (contractno,))
		served = self.cursor.fetchone()
		self.cursor.execute('SELECT Weight FROM CustomerContract WHERE ContractNo = ?', (contractno,))
		user = self.cursor.fetchone()
		newr = user[0]-served[0]
		self.cursor.execute('UPDATE CustomerContract SET Served =?, Remaining=? WHERE ContractNo = ?', (served[0], newr, contractno))
		self.connection.commit()
	
	def delivery_retrieval(self, contractno):
		self.cursor.execute('SELECT * FROM Delivery WHERE ContractNo = ?', (contractno, ))
		b = self.cursor.fetchall()
		out = []
		for x in b:
			out.append([x[0],x[15],x[2],x[3],x[4],x[5],x[6],x[7],x[8],x[9],x[10],x[11],x[12],x[13],x[14],x[1]])
		return out
	
	def add_delivery(self, entries):
		self.cursor.execute('INSERT INTO Delivery (DRNumber, Date, CauayanWeight, ManilaWeight, Reseco, TruckScale, UnloadFee, Lab, Tax, Other, NetAmount, CheckNo, PaymentDetails, PlateNo, TruckName, ContractNo) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', entries)
		self.connection.commit()
		
	def update_delivery(self, entries):
		self.cursor.execute('UPDATE Delivery SET Date=?, CauayanWeight=?, ManilaWeight=?, Reseco=?, TruckScale=?, UnloadFee=?, Lab=?, Tax=?, Other=?, NetAmount=?, CheckNo=?, PaymentDetails=?, PlateNo=?, TruckName=? WHERE DRNumber =?', (entries[1],entries[2],entries[3],entries[4],entries[5],entries[6],entries[7],entries[8],entries[9],entries[10],entries[11],entries[12],entries[13],entries[14], entries[0]))
		self.connection.commit() 
		
	def cropsinvolve_retrieval(self, contractno):
		self.cursor.execute('SELECT * FROM DeliveryDetails WHERE ContractNo = ?', (contractno, ))
		b = self.cursor.fetchall()
		out = []
		for x in b:
			out.append([x[2],x[3],x[4],x[5],x[6],x[7],x[8],x[9],x[10]])
		return out
	
	def deliverycrops_retrieval(self, drnumber):
		self.cursor.execute('SELECT * FROM DeliveryDetails WHERE DRNumber=?', (drnumber, ))
		b = self.cursor.fetchall()
		out = []
		for x in b:
			out.append([x[2],x[3],x[4],x[5],x[6],x[7],x[8],x[9],x[10], x[11]])
		return out
		
	def add_deliverycrops(self, entries, companyid, date):
		self.cursor.execute('INSERT INTO DeliveryDetails (DRNumber, Crop, Variety, Grade, UnitCost, Quantity, Total, CauayanWeight, ManilaWeight, Reseco, TransactionId, ContractNo) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)', entries)
		self.cursor.execute('SELECT Company From CustomerCompany WHERE CompanyId=?', (companyid,))
		name = self.cursor.fetchone()
		self.inventory_add(entries[10], date, entries[1], entries[2], entries[3], int(1), int(entries[7]), name[0])
		self.connection.commit()
		
	def update_deliverycrops(self, entries, companyid, date):
		self.cursor.execute('UPDATE DeliveryDetails SET Crop=?, Variety=?, Grade=?, UnitCost=?, Quantity=?, Total=?, CauayanWeight=?, ManilaWeight=?, Reseco=? WHERE TransactionId = ?', entries)
		self.cursor.execute('SELECT Company From CustomerCompany WHERE CompanyId=?', (companyid,))
		name = self.cursor.fetchone()
		self.inventory_update(entries[9], date, entries[0], entries[1], entries[2], 1, entries[6], name[0])
		self.connection.commit()
	
	def compute_cropweights(self, drnumber):
		self.cursor.execute('SELECT SUM(CauayanWeight), SUM(ManilaWeight) FROM DeliveryDetails WHERE DRNumber=?', (drnumber,))
		values = self.cursor.fetchone()
		reseco = values[0] - values[1]
		return values[0], values[1], reseco
	
	def variety_retrieval(self):
		cursor = self.cursor
		connection = self.connection
		cursor.execute('SELECT Variety FROM Variety')
		b = cursor.fetchall()
		c = []
		for i in b:
			c.append(i[0])
		return c

	def add_variety_to_db(self, result):
		cursor = self.cursor
		connection = self.connection
		cursor.execute('INSERT into Variety (Variety) values (?)', (result,))
		connection.commit()

	def edit_variety_in_db(self, newvalue, oldvalue):
		self.cursor.execute('UPDATE Variety SET Variety = ? WHERE Variety = ?', (newvalue, oldvalue))
		self.connection.commit()

	def get_varietylist(self):
		self.cursor.execute('SELECT Variety FROM Variety')
		out = self.cursor.fetchall()
		print out
		return out

	def get_newclientid(self):
		self.cursor.execute('SELECT Value FROM ID_Lookup WHERE Type = "ClientID"')
		client_id = self.cursor.fetchone()[0]+1
		self.cursor.execute('UPDATE ID_Lookup SET Value = ? WHERE Type = \'ClientID\'', (client_id,))
		self.connection.commit()
		
		return client_id
	
	def get_newcompanyid(self):
		self.cursor.execute('SELECT Value FROM ID_Lookup WHERE Type = "CompanyID"')
		company_id = self.cursor.fetchone()[0]+1
		self.cursor.execute('UPDATE ID_Lookup SET Value = ? WHERE Type = "CompanyID"', (company_id,))
		self.connection.commit()
		
		return company_id
		
	def get_newtransactionid(self):
		self.cursor.execute('SELECT Value FROM ID_Lookup WHERE Type = "TransactionId"')
		transaction_id = self.cursor.fetchone()[0]+1
		self.cursor.execute('UPDATE ID_Lookup SET Value = ? WHERE Type = "TransactionId"', (transaction_id,))
		self.connection.commit()
		return transaction_id
		
	def get_newdrnumber(self):
		self.cursor.execute('SELECT Value FROM ID_Lookup WHERE Type = "DRNumber"')
		drnumber = self.cursor.fetchone()[0]+1
		self.cursor.execute('UPDATE ID_Lookup SET Value = ? WHERE Type = "DRNumber"', (drnumber,))
		self.connection.commit()
		return drnumber
		
	def get_newbalanceid(self):
		self.cursor.execute('SELECT Value FROM ID_Lookup WHERE Type = "BalanceID"')
		balanceid = self.cursor.fetchone()[0]+1
		self.cursor.execute('UPDATE ID_Lookup SET Value = ? WHERE Type = "BalanceID"', (balanceid,))
		return balanceid
		
	
		
	def compute_clientbalance(self, id):
		self.cursor.execute('SELECT TOTAL(ComputedTotal) From ClientBalance WHERE ClientId = ?', (id,))
		balance = self.cursor.fetchone()
		self.cursor.execute('SELECT TOTAL(Total) From ClientPayment WHERE ClientId = ?', (id,))
		payment = self.cursor.fetchone()
		remaining = payment[0] - balance[0]
		return remaining
		
	def assign_password(self, hash):
		self.cursor.execute('UPDATE Administrator SET Password=? WHERE TYPE = "Admin"', (hash,))
		self.connection.commit()
		
	def get_password(self):
		self.cursor.execute('SELECT Password FROM Administrator WHERE TYPE = "Admin"')
		out = self.cursor.fetchone()
		return out[0] 
		
		
